﻿CREATE PROCEDURE [dbo].[proc_Assets_Names_Company_Getlist]
	(
		@CategoryId int,
		@CompanyId VARCHAR(500),
		@typeid int
	)
AS
	Declare @strsql VARCHAR(5000)
	SET @strsql='
	;WITH list as (Select ROW_NUMBER() OVER (ORDER BY Sort)AS Row,
	Id,
	CategoryId,
	(Select Title From Assets_Categorys Where Id=Assets_Names.CategoryId) As CategoryName,
	GId,
	Title,
	Sort,
	typeid,
	(case isnull(typeid,0) when 1 then ''固定公用类'' when 2 then ''分配使用类'' when 3 then ''耗材低值类''  end) as typename,
	(Select Count(A.Id) From Assets A Where ('''+@CompanyId+''' = ''0'' or  A.CompanyId in ('+@CompanyId+')) and A.StateId = 1 and Assets_Names.Id=A.NameId  ) AS SyCount,
	(Select Count(A.Id) From Assets A Where  ('''+@CompanyId+''' = ''0'' or A.CompanyId in ('+@CompanyId+'))and A.StateId = 2 and Assets_Names.Id=A.NameId   ) AS XzCount,
	(Select Count(A.Id) From Assets A Where  ('''+@CompanyId+''' = ''0'' or A.CompanyId in ('+@CompanyId+'))and A.StateId = 3 and Assets_Names.Id=A.NameId  and isapplybf=1 ) AS BfCount,
	IsNull((Select Sum(A.Prices) From Assets A Where  ('''+@CompanyId+''' = ''0'' or A.CompanyId in ('+@CompanyId+')) and Assets_Names.Id=A.NameId and A.StateId in (1,2) ),0) AS Price
	,'''+@CompanyId+'''  as CompanyId
	              From Assets_Names Where ('+CONVERT(varchar, @CategoryId)+'=0 Or CategoryId='+CONVERT(varchar, @CategoryId) +') and 
	              ('+CONVERT(varchar, @typeid)+'=0 Or typeid='+CONVERT(varchar, @typeid) +')
	)
	Select * From list order by Sort
	'
	--PRINT (@strsql)
	EXEC(@strsql)
	RETURN
